/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');
var async  = require( 'async'); // 同步控制


// Schema 结构
var ItbItemsModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //`item_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品id',
  //`item_no` varchar(50) NOT NULL DEFAULT '' COMMENT '商品编码',
  //`item_name` varchar(100) NOT NULL DEFAULT '' COMMENT '商品名',
  //`desc` varchar(200) DEFAULT '' COMMENT '简介',
  //`type_id` bigint(20) NOT NULL COMMENT '商品分类，三级分类id',
  //`state` int(2) NOT NULL DEFAULT '0' COMMENT '状态：10编辑、20上架、30下架',
  //`thumbs` text COMMENT '缩略图JSON数组',
  //`design_id` bigint(20) DEFAULT NULL COMMENT '设计模版的id',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) NOT NULL,
  //`del_flg` int(1) NOT NULL DEFAULT '0',

};
ItbItemsModel.prototype = new ItbBaseModel();

// 取得表名
ItbItemsModel.prototype.getTableName = function(){
    return 'itb_items';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbItemsModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'item_id='+this.item_id;
}

// 获取印品列表的简要信息
// 参数: typeM	 二级分类   40100: 名片  40200: 传单
// 参数: pageNum	 请求页
// 参数: pageSize	 请求页大小
// 参数：callback 回调，参数如下：
//          error:错误
//          result:多行数据
ItbItemsModel.prototype.findSummaryInfoByTypeM = function(typeM, pageNum, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbItemsModel.findSummaryInfoByTypeM Err:'+err);
        }

        // Use the connection
        var sql = ' SELECT x1.id, x1.price, x1.thumb, x1.w, x1.h, x1.theme_thumb, x1.title, x1.update_time, '+
            '   CASE WHEN st.show_apply_cnt>0 THEN st.show_apply_cnt '+
            '        WHEN st.apply_cnt>0 THEN st.apply_cnt '+
            '        ELSE 0 END AS useCount '+
            ' FROM '+
            ' (select i.item_id as `id`,  dd.design_id, '+
            '        min(qp.min) , '    +
            '        concat("印刷 ",ROUND(qp.price/100, 2),"元/",qp.show ,qp.unit) as price,    '+
            '        i.theme_thumb ,   '+// added 2017-06-22
            // '        func_parse_string_array(i.thumbs,0) as thumb ,   '+ // remove by jackLiu 2017-06-26
            // add by jackLiu 2017-06-26
            '        func_parse_string_array(get_item_or_temp_thumb(i.item_id, 1), 0) as thumb ,   '+
            '        func_parse_string_array(get_item_or_temp_thumb(i.item_id, 1), 1) as w ,   '+
            '        func_parse_string_array(get_item_or_temp_thumb(i.item_id, 1), 2) as h ,   '+
            // add end
            '        i.item_name as title,    '+
            '        i.update_time    '+
            ' from itb_qty_price qp, '+
            '   (select qp2.pp_id, min(qp2.min) as valmin from itb_qty_price qp2 where qp2.del_flg=0 group by qp2.pp_id) qp3, '+
            ' 	itb_print_param pp, '+
            ' 	itb_print_rel pr, '+
            ' 	itb_items i, '+
            '   tg_design_doc  dd ' +
            ' where qp.`del_flg`=0 '+
            ' and pp.`del_flg`=0 '+
            ' and pr.`del_flg`=0 '+
            ' and i.`del_flg`=0 '+
            ' AND qp.pp_id=qp3.pp_id and qp.min=qp3.valmin '+// 关联qp3取最小价格 added 2017-5-12
            ' and qp.`pp_id`= pp.`pp_id` '+
            ' and pp.`pp_id`= pr.`pp_id` '+
            ' and pr.`item_id`=i.`item_id` '+
            ' and dd.item_id = i.item_id ' +
            ' and dd.publish = 1 ' +
            ' and i.del_flg=0 and i.state=20 '+
            ' and i.`type_id` in ( '+
            ' 		select t.`type_id`  '+
            ' 		from tg_type t  '+
            ' 		where t.`del_flg`=0 '+
            ' 		and t.`parent`=  '+ typeM +' ) '+
            ' group by `id` '+
            ' order by update_time desc, id desc '+
            ' limit '+pageNum*pageSize+','+pageSize +
            ' ) x1 '+
            ' LEFT JOIN itb_tmpl_stats st ON st.del_flg=0 AND st.type=4 AND (st.tmpl_id=x1.design_id OR st.item_id=x1.id) '+// added 2017-5-12
        ' order by x1.update_time desc, x1.id desc ';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
};


//------------------------------------------------------------------
/* 根据type获取印品信息
 * 参数 bForCnt: true [仅用于取count]; false [取数据]
 * */
function sql_getItemInfoByType(bForCnt, typeM, typeS, bWithoutVip) {
    var sql = ' SELECT x1.*, '+
            ' IFNULL(st.apply_cnt,0) AS apply_cnt, IFNULL(st.show_apply_cnt,0) AS show_apply_cnt, '+
            ' IFNULL(st.view_cnt,0) AS view_cnt, IFNULL(st.show_view_cnt,0) AS show_view_cnt ';
    if(bForCnt) {
        sql = ' SELECT COUNT(*) AS cnt ';
    }
    sql += ' FROM ( '+
        ' SELECT t.design_id, t.item_id AS goods_id, func_parse_string_array(t.thumbs,0) AS thumb, '+
        ' t.update_time, t.create_time, tp.top_parent, s.width, s.height, '+
        ' t.item_name AS goods_name, MIN(qp.min), '+
        ' dd.vip, '+// added 2017-07-20 v6.0
        ' concat("印刷 ",TRUNCATE(qp.price/100, 2),"元/",qp.show ,qp.unit) as goods_price '+
        ' FROM itb_items t, tg_design_doc dd, tg_type tp, TG_SIZE s, '+
        '   itb_print_rel pr, itb_print_param pp, itb_qty_price qp, '+
        '   (select qp2.pp_id, min(qp2.min) as valmin from itb_qty_price qp2 where qp2.del_flg=0 group by qp2.pp_id) qp3 '+
        ' WHERE t.del_flg=0 AND t.state=20 AND t.design_id=dd.design_id and dd.del_flg=0 AND dd.size_id=s.size_id AND s.del_flg=0 '+
        ' AND pr.del_flg=0 AND pr.item_id=t.item_id '+
        ' AND pp.del_flg=0 AND pp.pp_id=pr.pp_id '+
        ' AND qp.del_flg=0 AND qp.pp_id=pp.pp_id AND qp.pp_id=qp3.pp_id and qp.min=qp3.valmin '+// 关联qp3取最小价格 added 2017-5-12
        ' AND tp.del_flg=0 AND tp.type_id=t.type_id ';
    if (typeM>0) {
        sql += ' AND tp.parent= '+typeM;
    }
    if(typeS>0) {
        sql += ' AND tp.type_id= '+typeS;
    }
    if(bWithoutVip) {
        // 不包括vip商品
        sql += ' AND dd.vip=0 ';
    }
    sql += ' GROUP BY t.item_id '+
        ' ORDER BY t.create_time DESC ) x1 ';

    // 取统计数据 added 2017-5-12
    sql += ' LEFT JOIN itb_tmpl_stats st '+
        ' ON st.del_flg=0 AND st.type=x1.top_parent AND st.tmpl_id=x1.design_id ';

    return sql;
}

ItbItemsModel.prototype.getItemInfoByType=function(typeM,typeS,skipCnt,pageSize,callback,bWithoutVip){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbItemsModel.getItemInfoByType Err:'+err);
            return callback(err,null);
        }

        var sql = sql_getItemInfoByType(false, typeM, typeS, bWithoutVip);

        if(typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        //var sqlValue = [category, code];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
};

ItbItemsModel.prototype.getItemInfoByTypeCnt=function(typeM,typeS,callback,bWithoutVip){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbItemsModel.getItemInfoByTypeCnt Err:'+err);
            return callback(err,null);
        }

        var sql = sql_getItemInfoByType(true, typeM, typeS, bWithoutVip);

        //var sqlValue = [category, code];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            if(items && items.length>0){
                callback(error, Number(items[0].cnt||'0'));
            }else{
                callback(error, 0);
            }
        });

    });
};
//------------------------------------------------------------------


// 获取印品列表的简要信息
// 参数: id	印品 id
// 参数：callback 回调，参数如下：
//          error:错误
//          result:带设计师信息的印品model
ItbItemsModel.prototype.findPrintItemAndUserInfo = function(id, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbItemsModel.findPrintItemAndUserInfo Err:'+err);
        }

        // Use the connection
        var sql =' select i.*,u.user_id,u.user_name,u.nickname,u.head_url '+
            ' from itb_items i, '+
            ' tg_design_doc d, '+
            ' itb_user u '+
            ' where i.item_id= '+id+
            ' and i.del_flg=0 '+
            ' and d.del_flg=0 '+
            ' and u.del_flg=0 '+
            ' and i.design_id=d.design_id '+
            ' and d.user_id=u.user_id';


        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            callback(error, result);
        });
    });
}


//------------------------------------------------------------------
// 获取sql，
/**
 * 获取印品商品列表
 *
 * @param bForCnt sql 用途, 是否用于获取数量
 * @param typeM 过滤的二级分类
 * @param colors 过滤颜色
 * @param tags 过滤的标签
 * @param withTotalPage 获取总页数
 * @param pageNum 请求页码数
 * @param pageSize 请求页容量
 * @param vipFlg 是否获取 vip 模板标记, 不传或false 认为不获取, 传递 true 标记获取 vip印品商品
 *
 * */
function sql_getFilterListForWebOfPrint  (bForCnt,typeM,colors,tags, withTotalPage, pageNum, pageSize, vipFlg){
    // 表a:查询出模版的基本信息
    var selectSql = ' distinct i.item_id as id, '+
                    ' i.item_name AS title, '+
                    ' 4 AS typeL, '+
                    ' `func_parse_string_array`(i.thumbs, 0) AS thumb  ' +
                    ' , dd.vip'; // add 2017-07-21 增加 vip 返回字段
    var fromSql =
                    '  itb_items i   '+
                    ' ,tg_type t ' +
                    ' ,tg_design_doc dd ';

    var whereSql =  ' i.del_flg = 0 '+
        ' AND i.state = 20 '+
        ' AND i.type_id = t.type_id '+
        '     AND dd.item_id = i.item_id' +
        '   AND dd.publish = 1 ' +
        '   AND dd.del_flg = 0 ';
    if(!vipFlg) {
        whereSql += '   AND dd.vip = 0 ';  // add 2017-07-21 增加 vip 过滤
    }
    if(typeM && typeM != -1){
        whereSql += ( ' AND t.`parent` = '+ typeM );
    }
    // 获取全部印品
    if(typeM == -1) {
        whereSql += ( ' AND t.`top_parent` = '+ 4);
    }

    var orderSql = ' ORDER BY i.create_time DESC  ';
    var limitSql = ' LIMIT '+pageNum*pageSize+','+pageSize;
    if(colors && colors.length>0){
        fromSql = fromSql +
            '  ,tg_keyword k '+
            '  ,tg_keyword_r kr ';

        whereSql = whereSql +
            ' AND k.`type`=4 '+
            ' AND k.key_id in (' +colors.join(',')+ ') '+
            ' AND k.key_id=kr.key_id '+
            ' AND kr.doc_id=i.design_id ';
    }
    if(bForCnt){
        // 如果是查询总条数
        var sql_a =     ' SELECT COUNT(*)  AS count  FROM '+
            ' ('+
            '   SELECT '+ selectSql+
            '   FROM  '+ fromSql+
            '   WHERE ' + whereSql+
            ' ) x';
        return sql_a;
    }else{
        var sql_a =     ' SELECT '+ selectSql+
            ' FROM  '+ fromSql+
            ' WHERE ' + whereSql+
            orderSql+
            limitSql;

        // 表b:查询出最小价格
        var sql_b =
            ' 	SELECT pr.item_id,  '+
            ' 			MIN(qp.price) AS min_price,  '+
            ' 			qp.show, qp.`unit` '+
            ' 	FROM itb_print_rel pr,  '+
            ' 			itb_print_param pa,  '+
            ' 			itb_qty_price qp '+
            ' 	WHERE pa.del_flg = 0 '+
            ' 			AND pr.del_flg = 0 '+
            ' 			AND qp.del_flg = 0 '+
            ' 			AND pa.pp_id = pr.pp_id '+
            ' 			AND qp.pp_id = pr.pp_id '+
            ' 	GROUP BY item_id ';

        // 表a 左连接 表b
        var sql =
            '  SELECT a.*,  '+
            '         CASE b.min_price WHEN b.min_price THEN CONCAT("印刷 ", ROUND(b.min_price/100,2), "元/", b.show, b.unit) ELSE "免费" END AS price '+
            '  FROM ( '+
            sql_a+
            '  ) a '+
            '  LEFT JOIN ( '+
            sql_b+
            '  ) b  '+
            '  ON a.id = b.item_id  ';

        return sql;
    }

}
/**
 *  获取印品列表
 *  参数：typeM 二级分类ID
 *  参数：colors	所需颜色分类 id 数组的 json 字符串, 鉴于网页端可随时发布, 颜色对应可直接写死, 如需多选, 将所选验证传递即可, 如: "[1, 2, 3]"
 *  参数: tags	所需热门标签 id 数组的 json 字符串, 鉴于网页端可随时发布, 标签对应可直接写死, 如需多选, 将所选标签传递即可, 如: "[1, 2]"
 *  参数: withTotalPage 是否需要总页数
 *  参数: pageNum 请求页码, 从 0 开始
 *  参数: pageSize 请求页大小
 *  参数：callback，参数如下：
 *          error：null表示无错误
 *          data：数组，例如：
 *              [
 *                      {
     *                          "id":515,
     *                          "thumb":"http://itbour-generate.oss-cn-hangzhou.aliyuncs.com/image/U3/2017/02/07/153525097_fBkxBZpSet3FHB9K9CWS/0.jpg",
     *                          "typeL":1,
     *                          "title":"大声说爱你情人节祝福模板",
     *                          "desc":"印刷 20元／2盒"
     *                      }
 *          ]
 */

ItbItemsModel.prototype.getFilterListForWebOfPrint  = function(typeM,colors,tags, withTotalPage, pageNum, pageSize,callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {

        var sql  = sql_getFilterListForWebOfPrint(false,typeM,colors,tags, withTotalPage, pageNum, pageSize );

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result);
            } else{
                callback(error, null);
            }

        });
    });
}
/**
 *  获取印品数量
 *  参数：typeM 二级分类ID
 *  参数：colors	所需颜色分类 id 数组的 json 字符串, 鉴于网页端可随时发布, 颜色对应可直接写死, 如需多选, 将所选验证传递即可, 如: "[1, 2, 3]"
 *  参数: tags	所需热门标签 id 数组的 json 字符串, 鉴于网页端可随时发布, 标签对应可直接写死, 如需多选, 将所选标签传递即可, 如: "[1, 2]"
 *  参数: withTotalPage 是否需要总页数
 *  参数: pageNum 请求页码, 从 0 开始
 *  参数: pageSize 请求页大小
 *  参数：callback，参数如下：
 *          error：null表示无错误
 *          data：数组，例如：
 *              [
 *                      {
     *                          "id":515,
     *                          "thumb":"http://itbour-generate.oss-cn-hangzhou.aliyuncs.com/image/U3/2017/02/07/153525097_fBkxBZpSet3FHB9K9CWS/0.jpg",
     *                          "typeL":1,
     *                          "title":"大声说爱你情人节祝福模板",
     *                          "desc":"2.00元"
     *                      }
 *          ]
 */

ItbItemsModel.prototype.getFilterListCountForWebOfPrint  = function(typeM,colors,tags, withTotalPage, pageNum, pageSize,callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {

        var sql  = sql_getFilterListForWebOfPrint(true,typeM,colors,tags, withTotalPage, pageNum, pageSize );

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0){
                callback(error, result[0].count);
            }else{
                callback(error, 0);
            }

        });
    });
}
//--------------------------------end-------------------------------

/**
 *  找到印刷商品的分类对应的配件分类下符合尺寸S的配件商品取出
 *  参数：typeM 二级分类ID
 *  参数：colors	所需颜色分类 id 数组的 json 字符串, 鉴于网页端可随时发布, 颜色对应可直接写死, 如需多选, 将所选验证传递即可, 如: "[1, 2, 3]"
 *  参数: tags	所需热门标签 id 数组的 json 字符串, 鉴于网页端可随时发布, 标签对应可直接写死, 如需多选, 将所选标签传递即可, 如: "[1, 2]"
 *  参数: withTotalPage 是否需要总页数
 *  参数: pageNum 请求页码, 从 0 开始
 *  参数: pageSize 请求页大小
 *  参数：callback，参数如下：
 *          error：null表示无错误
 *          data：数组，例如：
 *              [
 *                      {
     *                          "id":515,
     *                          "thumb":"http://itbour-generate.oss-cn-hangzhou.aliyuncs.com/image/U3/2017/02/07/153525097_fBkxBZpSet3FHB9K9CWS/0.jpg",
     *                          "typeL":1,
     *                          "title":"大声说爱你情人节祝福模板",
     *                          "desc":"2.00元"
     *                      }
 *          ]
 */

ItbItemsModel.prototype.getAccessoryByPrintTypeAndSize  = function(typeSOfPrint, sizeId, pageNum, pageSize,callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {

        //var sql  =
        //    ' select i.item_id as `id`,  '+
        //    ' 		i.item_name as `name`, '+
        //    ' 		i.desc as `desc`, '+
        //    ' 		i.thumbs as `thumbs`, '+
        //    '       q.show, q.price '+// added 2017/04/08
        //    ' from itb_items i '+
        //    ' 	, itb_print_rel r '+
        //    ' 	, itb_print_param p '+
        //    '   , itb_qty_price q '+
        //    ' where i.del_flg=0 '+
        //    ' and i.type_id in( '+
        //    ' 	select `func_find_accessory_type`('+typeSOfPrint+') '+
        //    ' ) '+
        //    ' and i.`item_id`=r.`item_id` '+
        //    ' and r.del_flg=0 and r.`pp_id`= p.`pp_id` '+
        //    ' and p.del_flg=0 and p.`size_id`= '+sizeId+
        //    ' and q.del_flg=0 and q.pp_id=p.pp_id '+
        //    ' order by i.item_id, q.min '+
        //    ' limit ' +pageNum*pageSize+','+pageSize;



        var sql = ' call proc_find_accessory('+typeSOfPrint+','+sizeId+','+pageNum+','+pageSize+') ';


        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();

            if(result && result.length>0 && result[0] && result[0].length>0){
                callback(error, result[0]);
            }else{
                callback(error, 0);
            }

        });
    });
}

//---------------------------------------------------------------------
// todo 新增接口 begin
/*
*	根据 印品商品id 查找 配件三级分类下 的配件商品id的集合（如过某配件三级分类下有多个配件商品，则取第一个）
* */
ItbItemsModel.prototype.getItemAccessoryItemsId = function(itemId, callback){

    pool.pool.getConnection(function(err, connection) {
        // 链接出错
        if(err){
            console.log('ItbItemsModel [getItemAccessoryItemsId] getConnection err:' + err);
            return callback(err, null);
        }
        var sql  = "SELECT func_find_accessory_item("+ itemId +") AS ids";
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();
            if(error){
                console.log('ItbItemsModel [getItemAccessoryItemsId] query err: ' + callback);
            }
            if(result && result.length > 0){
                return callback(error, result[0].ids);
            } else {
                return callback(error, null);
            }

        });
    });
};

//---------------------------------------------------------------------
// 获取商品对应的模版信息
ItbItemsModel.prototype.getItemDesignInfo = function(itemId, callback) {
    pool.pool.getConnection(function(err, connection) {
        // 链接出错
        if(err){
            return callback(err, null);
        }
        var sql  =
            " SELECT t.parent AS typeM, " +
            "        t.top_parent AS typeL, " +
            "        t.type_id AS typeS, " +
            "        d.* " +
            " FROM itb_items it, "+
            "      tg_design_doc d, " +
            "      tg_type t " +
            " WHERE it.del_flg=0 AND it.item_id= " + itemId +
            "   AND d.del_flg=0 AND d.item_id=it.item_id "+
            "   AND t.del_flg=0 AND t.type_id=d.type_id ";
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();
            if(error || !result || result.length <= 0) {
                return callback(error, null);
            }
            return callback(error, result[0]);
        });
    });
};

// todo 新增接口 end
/**
 * 获取商品对应模板的分类信息
 *
 * */
//-------------------------获取相似商品唯一标识-----------------------
ItbItemsModel.prototype.getItemTypeInfo = function(id, callback) {
    pool.pool.getConnection(function(err, connection) {
        // 链接出错
        if(err){
            return callback(err, null);
        }
        var sql  =
            " SELECT t.parent AS typeM, " +
            "        t.top_parent AS typeL, " +
            "        t.type_id AS typeS, " +
            "        t.* " +
            " FROM tg_design_doc d, " +
            "      tg_type t " +
            " WHERE d.item_id= " + id +
            "   AND t.type_id=d.type_id ";
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            //console.log();
            if(error || !result || result.length <= 0) {
                return callback(error, null);
            }
            return callback(error,result[0]);
        });
    });
};

//--------------------------------end-------------------------------

// 导出
module.exports = ItbItemsModel;